package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DomainLayer.BradenScale;
import DomainLayer.NutritionRiskScheme;




public class DBNutRiskScheme implements IFDBNutRiskScheme {

private Connection con;
	
	public DBNutRiskScheme(){
		con = DbConnection1.getInstance().getDBcon();
		}
	
	
	public int createScheme(NutritionRiskScheme scheme) {
		int rc = -1;
		
		String query = "INSERT INTO nutritionalRiskSchema (nutriskName, impnutritionstatus, severityofdisease) VALUES ('"+
				scheme.getName() + "', " +
				scheme.getImpNutritionStatus() + ", " +
				scheme.getSeverityOfDisease() + ")";
		
		
		System.out.println("insert : " + query);
		
		try{Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(query);
				stmt.close();
		}catch(SQLException ex){
			System.out.println("insertion was not correct");

		}
		
		return rc;
	}


	
	
	public int deleteScheme(String schemeName) {
		
		int rc = -1;

		String query = "DELETE FROM NutritionalRiskSchema WHERE nutriskName ='" + schemeName
				+ "'";
		System.out.println(query);
		try { // delete from OrderLine
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Delete exception in nutritional risk scheme db: " + ex);
		}
		return rc;
	}
		

	
	public NutritionRiskScheme getScheme(String schemeName) {
		String wClause = "nutriskName = '" + schemeName + "' ";
		return singleWhere(wClause);
	}
	

	
	
	public int updateScheme(NutritionRiskScheme scheme) {
		
		int rc = -1;
		
		String query = "UPDATE nutritionalriskschema SET " + "impnutritionstatus='" + scheme.getImpNutritionStatus()
					+ "',severityofdisease= ' " + scheme.getSeverityOfDisease() + "'" +
				
					"WHERE nutriskName= '" + scheme.getName() + "'";
		System.out.println("Update query: " + query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			
			stmt.close();	
		}//end try
		catch (Exception ex){
			System.out.println("Update exception in nutritional risk scheme db: " + ex);
		} 
			return rc;
				
	}
	

	
	private NutritionRiskScheme singleWhere(String wClause) {
		ResultSet results;
		NutritionRiskScheme nrsObj = new NutritionRiskScheme();

		String query = buildQuery(wClause);
		System.out.println(query);
		try { // read the OrderLine from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				nrsObj = buildNRS(results);

				stmt.close();

			} else { // 
				nrsObj = null;
			}
		}// end try
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return nrsObj;
	}

	
	private NutritionRiskScheme buildNRS(ResultSet results) {
		NutritionRiskScheme nrsObj = new NutritionRiskScheme();
		try {

			nrsObj.setName(results.getString("nutriskName"));
			nrsObj.setImpNutritionStatus(results.getInt("impnutritionstatus"));
			nrsObj.setSeverityOfDisease(results.getInt("severityofdisease"));
			
		

		} catch (Exception e) {
			System.out.println("error in building the nutrition risk scheme object");
		}
		return nrsObj;
	}
	
	 private String buildQuery(String wClause)
	 {
	     String query="SELECT nutriskName, impnutritionstatus, severityofdisease FROM nutritionalriskschema";
	  
	  if (wClause.length()>0)
	   query=query+" WHERE "+ wClause;
	   
	  return query;
	 }
	 
	 private ArrayList<NutritionRiskScheme> miscWhere(String wClause) {
			ResultSet results;
			ArrayList<NutritionRiskScheme> list = new ArrayList<NutritionRiskScheme>();

			String query = buildQuery(wClause);

			try { // read the employee from the database
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				while (results.next()) {
					NutritionRiskScheme scheme = new NutritionRiskScheme();
					scheme = buildNRS(results);
					list.add(scheme);
				}// end while
				stmt.close();

			}// slut try
			catch (Exception e) {
				System.out.println("Query exception - select: " + e);
				e.printStackTrace();
			}
			return list;

		}
	 
	 public ArrayList<NutritionRiskScheme> getAllNutritionRiskSchemes()
	 {
		 return miscWhere("");
		 
		 
	 }
	
}//end class
